﻿using Dapper;
using Domain.Entity;
using Domain.Entity.ic;
using Domain.Entity.tr;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class Check_dRepository : ICheck_dRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        //添加盘点明细
        public async Task<int> AddCheck_d(Check_d check_D)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[ic_check_d](ic_id, proc_id, proc_name, icd_num, profitandloss_num, icd_status, icd_price, profitandloss_total, instock_status, outstock_status, remark)
                                values({check_D.ic_id},{check_D.proc_id},'{check_D.proc_name}',{check_D.icd_num},'{check_D.profitandloss_num}','{check_D.icd_status}','{check_D.icd_price}','{check_D.profitandloss_total}',{check_D.instock_status},{check_D.outstock_status},'{check_D.remark}');";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //删除盘点明细
        public async Task<int> DeleteCheck_d(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"delete from [dbo].[ic_check_d] where icd_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //查询盘点明细
        public async Task<List<Check_d>> GetCheck_Ds(int icid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ic_check_d] where ic_id={icid}";
                return sqlConnection.Query<Check_d>(sql).ToList();
            }
        }


        //查询盘盈盘亏明细
        public async Task<List<Check_d>> GetCheck_DsYingKui(int icid, string status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ic_check_d] where ic_id={icid} and icd_status='{status}'";
                return sqlConnection.Query<Check_d>(sql).ToList();
            }
        }


        //修改盘点明细
        public async Task<int> UpdateCheck_d(Check_d check_D)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ic_check_d] set ic_id={check_D.ic_id}, proc_id={check_D.proc_id}, proc_name='{check_D.proc_name}', icd_num={check_D.icd_num}, profitandloss_num={check_D.profitandloss_num}, icd_status='{check_D.icd_status}', 
                                icd_price='{check_D.icd_price}', profitandloss_total='{check_D.profitandloss_total}', instock_status={check_D.instock_status}, outstock_status={check_D.outstock_status}, remark='{check_D.remark}'  where icd_id={check_D.icd_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改出入库状态
        public async Task<int> UpdateStatus(string name, int status, int icid, int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ic_check_d] set {name}={status} where ic_id={icid} and proc_id={procid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //统计盘点
        public async Task<FenYe<CheckTJResult>> GetCheckTJResult(FenYe<CheckTJResult> fenYe,
            string? proc_code, string? ic_code, string? ic_type, string? create_name,
            int? type_id, string? proc_primary_unit, int? wh_id, string? ic_user,
            string? createstartTime, string? createendTime,
            decimal? checkprofitNumMin, decimal? checkprofitNumMax,
            decimal? checklossesNumMin, decimal? checklossesNumMax,
            decimal? checkprofitTotalMin, decimal? checkprofitTotalMax,
            decimal? checklossesTotalMin, decimal? checklossesTotalMax,
            int[]? icStatusArray)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 初始化基础WHERE条件
                var whereBuilder = new StringBuilder("header_is_delete = 0 AND product_is_delete = 0");

                // 添加所有参数条件 (按参数顺序)
                // 1. 产品编码/名称条件
                if (!string.IsNullOrEmpty(proc_code))
                {
                    whereBuilder.Append($" AND (proc_name LIKE '%{proc_code}%' OR proc_code LIKE '%{proc_code}%')");
                }

                // 2. 盘点单号条件
                if (!string.IsNullOrEmpty(ic_code))
                {
                    whereBuilder.Append($" AND ic_code LIKE '%{ic_code}%'");
                }

                // 3. 盘点类型条件 (修复缺少的右括号)
                if (!string.IsNullOrEmpty(ic_type))
                {
                    whereBuilder.Append($" AND ic_type LIKE '%{ic_type}%'");
                }

                // 4. 创建人条件
                if (!string.IsNullOrEmpty(create_name))
                {
                    whereBuilder.Append($" AND create_name LIKE '%{create_name}%'");
                }

                // 5. 商品分类条件
                if (type_id.HasValue)
                {
                    whereBuilder.Append($" AND type_id = {type_id.Value}");
                }

                // 6. 主单位条件
                if (!string.IsNullOrEmpty(proc_primary_unit))
                {
                    whereBuilder.Append($" AND proc_primary_unit LIKE '%{proc_primary_unit}%'");
                }

                // 7. 仓库ID条件
                if (wh_id.HasValue)
                {
                    whereBuilder.Append($" AND wh_id = {wh_id.Value}");
                }

                // 8. 盘点人条件
                if (!string.IsNullOrEmpty(ic_user))
                {
                    whereBuilder.Append($" AND ic_user LIKE '%{ic_user}%'");
                }

                // 9. 创建时间范围条件
                if (!string.IsNullOrEmpty(createstartTime) && !string.IsNullOrEmpty(createendTime))
                {
                    whereBuilder.Append($" AND create_time BETWEEN '{createstartTime}' AND '{createendTime}'");
                }

                // 10. 盘盈数量范围
                if (checkprofitNumMin.HasValue)
                {
                    whereBuilder.Append($" AND checkprofit_num >= {checkprofitNumMin.Value}");
                }
                if (checkprofitNumMax.HasValue)
                {
                    whereBuilder.Append($" AND checkprofit_num <= {checkprofitNumMax.Value}");
                }

                // 11. 盘亏数量范围
                if (checklossesNumMin.HasValue)
                {
                    whereBuilder.Append($" AND checklosses_num >= {checklossesNumMin.Value}");
                }
                if (checklossesNumMax.HasValue)
                {
                    whereBuilder.Append($" AND checklosses_num <= {checklossesNumMax.Value}");
                }

                // 12. 盘盈金额范围
                if (checkprofitTotalMin.HasValue)
                {
                    whereBuilder.Append($" AND checkprofit_total >= {checkprofitTotalMin.Value}");
                }
                if (checkprofitTotalMax.HasValue)
                {
                    whereBuilder.Append($" AND checkprofit_total <= {checkprofitTotalMax.Value}");
                }

                // 13. 盘亏金额范围
                if (checklossesTotalMin.HasValue)
                {
                    whereBuilder.Append($" AND checklosses_total >= {checklossesTotalMin.Value}");
                }
                if (checklossesTotalMax.HasValue)
                {
                    whereBuilder.Append($" AND checklosses_total <= {checklossesTotalMax.Value}");
                }

                // 14. 盘点结果状态条件
                if (icStatusArray != null && icStatusArray.Length > 0)
                {
                    whereBuilder.Append($" AND ic_status IN ({string.Join(",", icStatusArray)})");
                }

                // 初始化分页参数
                var parameters = new DynamicParameters();
                parameters.Add("@pageSize", 10);  // 使用传入的分页大小
                parameters.Add("@keyName", "ic_id");
                parameters.Add("@tabelName", "vw_check_detail");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder.ToString());
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<CheckTJResult>(sql, parameters);

                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");

                return fenYe;
            }
        }
    }
}
